﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.Home
{
    public class HomeStatisticsDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static int getNoRoomsOccupied()
        {
            int result = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM RoomBooking WHERE bookingID NOT IN (SELECT bookingID FROM RoomCheckout)";

                result = (int)comm.ExecuteScalar();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int getTotalRooms()
        {
            int result = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM Room";

                result = (int)comm.ExecuteScalar();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int getTotalGuestNow()
        {
            int result = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT SUM(noOfAdults + noOfChildren) FROM RoomBooking WHERE bookingID NOT IN (SELECT bookingID FROM RoomCheckout)";

                object eS = comm.ExecuteScalar();
                if (eS != DBNull.Value) result = (int)eS;

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int getTodayCheckinCount()
        {
            int result = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM RoomBooking WHERE checkIn >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)) AND checkIn <  DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));";

                result = (int)comm.ExecuteScalar();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static int getDueCheckoutCount()
        {
            int result = 0;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT COUNT(*) FROM RoomBooking WHERE checkOut >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP)) AND checkOut <  DATEADD(DAY, 1, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP));";

                result = (int)comm.ExecuteScalar();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return result;
        }

        public static decimal getTotalRevenue()
        {
            decimal revenue = 0;

            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT SUM(netPayable) FROM Invoice";

                object eS = comm.ExecuteScalar();
                if (eS != DBNull.Value) revenue = (decimal)eS;

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return revenue;
        }
    }
}